package org.homeorganiser.flex.util;

public final class Constant {

	public final static String SQL_CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS USER ID INT AUTO_INCREMENT PRIMARY KEY, USER_ID VARCHAR(15),"
			+ "PASSWORD VARCHAR(15),FIRST_NAME VARCHAR(50),LAST_NAME VARCHAR(50),ADDRESS VARCHAR(50),CITY VARCHAR(50),STATE VARCHAR(20),ZIP VARCHAR(20),PHONE VARCHAR(50),"
			+ "EMAIL VARCHAR(50),SECQUES VARCHAR(100),SECANS VARCHAR(100))";

	public final static String SQL_CREATE_INVENTORY_TABLE = "CREATE TABLE IF NOT EXISTS INVENTORY (ID INT(11) NOT NULL AUTO_INCREMENT,INVENTORYNAME VARCHAR(20) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,DESCRIPTION VARCHAR(150) DEFAULT NULL,PRIMARY KEY  (ID))";
	public final static String SQL_CREATE_CATEGORY_TABLE = "CREATE TABLE IF NOT EXISTS CATEGORY (ID INT(11) NOT NULL AUTO_INCREMENT,CATEGORYNAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_inventory(INVENTORYTYPE),CONSTRAINT FK_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_SUBCATEGORY_TABLE = "CREATE TABLE IF NOT EXISTS SUBCATEGORY(ID INT(11) NOT NULL AUTO_INCREMENT,SUBCATEGORYNAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(1) DEFAULT NULL,ADDEDBY VARCHAR(50) DEFAULT NULL,CATEGORYID INT(11) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (Id),KEY FK_subcategory(CATEGORYID),KEY FK_inventory(INVENTORYTYPE), CONSTRAINT FK_subcategory FOREIGN KEY (CATEGORYID) REFERENCES CATEGORY (ID),CONSTRAINT FK_subCategory_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_GENRE_TABLE = "CREATE TABLE IF NOT EXISTS GENRE (ID INT(11) NOT NULL AUTO_INCREMENT,GENRENAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_genre_inventory(INVENTORYTYPE),CONSTRAINT FK_genre_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_LANGUAGE_TABLE = "CREATE TABLE IF NOT EXISTS LANGUAGE (ID INT(11) NOT NULL AUTO_INCREMENT,LANGANAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_lang_inventory(INVENTORYTYPE),CONSTRAINT FK_lang_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_TYPE_TABLE = "CREATE TABLE IF NOT EXISTS TYPE (ID INT(11) NOT NULL AUTO_INCREMENT,TYPENAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_type_inventory(INVENTORYTYPE),CONSTRAINT FK_type_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_SCREENSHOT_TABLE = "CREATE TABLE IF NOT EXISTS SCREENSHOT (ID INT(11) NOT NULL AUTO_INCREMENT,SCREENSHOTNAME VARCHAR(25) DEFAULT NULL,IMAGE LONGBLOB,INVENTORYID INT(11) DEFAULT  NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_screenshot_inventory(INVENTORYTYPE),CONSTRAINT FK_screenshot_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_QUALITY_TABLE = "CREATE TABLE IF NOT EXISTS QUALITY (ID INT(11) NOT NULL AUTO_INCREMENT,QUALITYNAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_quality_inventory(INVENTORYTYPE),CONSTRAINT FK_quality_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_LOCATION_TABLE = "CREATE TABLE IF NOT EXISTS LOCATION (ID INT(11) NOT NULL AUTO_INCREMENT,LOCATIONNAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_location_inventory(INVENTORYTYPE),CONSTRAINT FK_location_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	public final static String SQL_CREATE_PUBLICATION_TABLE = "CREATE TABLE IF NOT EXISTS PUBLICATION (ID INT(11) NOT NULL AUTO_INCREMENT,PUBLICATIONNAME VARCHAR(100) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(11) DEFAULT 1,ADDEDBY VARCHAR(25) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_publication_inventory(INVENTORYTYPE),CONSTRAINT FK_publication_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES INVENTORY (ID))";
	
	public final static String SQL_CREATE_BOOK_TABLE ="CREATE TABLE IF NOT EXISTS book (ID INT(11) NOT NULL AUTO_INCREMENT,BOOKNAME VARCHAR(100) DEFAULT NULL,AUTHORNAME VARCHAR(100) DEFAULT NULL,PRICE VARCHAR(25) DEFAULT NULL, COMMENTS VARCHAR(150) DEFAULT NULL,PUBLICATIONID INT(11) DEFAULT NULL,TYPEID INT(11) DEFAULT NULL,GENREID INT(11) DEFAULT NULL,LANGID INT(11) DEFAULT NULL,QUALITYID INT(11) DEFAULT NULL,LOCATIONID INT(11) DEFAULT NULL,SUBCATEGORYID INT(11) DEFAULT NULL,CATEGORYID INT(11) DEFAULT NULL,INVENTORYTYPE INT(11) DEFAULT NULL,ISACTIVE INT(1) DEFAULT '1',ADDEDBY VARCHAR(100) DEFAULT NULL,ADDEDON TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (ID),KEY FK_book_category (CATEGORYID),KEY FK_book_subcategory (SUBCATEGORYID),KEY FK_book_type (TYPEID),KEY FK_book_genre (GENREID),KEY FK_book_lang (LANGID),KEY FK_book_quality (QUALITYID),KEY FK_book_publication (PUBLICATIONID),KEY FK_book_location (LOCATIONID),KEY FK_book_inventory (INVENTORYTYPE),CONSTRAINT FK_book_category FOREIGN KEY (CATEGORYID) REFERENCES category (ID),CONSTRAINT FK_book_subcategory FOREIGN KEY (SUBCATEGORYID) REFERENCES subcategory (ID),CONSTRAINT FK_book_type FOREIGN KEY (TYPEID) REFERENCES type (ID),CONSTRAINT FK_book_genre FOREIGN KEY (GENREID) REFERENCES genre (ID),CONSTRAINT FK_book_lang FOREIGN KEY (LANGID) REFERENCES language (ID),CONSTRAINT FK_book_quality FOREIGN KEY (QUALITYID) REFERENCES quality (ID),CONSTRAINT FK_book_publication FOREIGN KEY (PUBLICATIONID) REFERENCES publication (ID),CONSTRAINT FK_book_location FOREIGN KEY (LOCATIONID) REFERENCES location (ID),CONSTRAINT FK_book_inventory FOREIGN KEY (INVENTORYTYPE) REFERENCES inventory (ID))"; 
    

	public final static String SQL_INSERT_INVENTORY_TABLE = "INSERT INTO INVENTORY (INVENTORYNAME,DESCRIPTION) VALUES(?,?)";
	public final static String SQL_INSERT_CATEGORY_TABLE = "INSERT INTO CATEGORY (CATEGORYNAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_SUBCATEGORY_TABLE = "INSERT INTO SUBCATEGORY (SUBCATEGORYNAME,INVENTORYTYPE,ADDEDBY,CATEGORYID) VALUES(?,?,?,?)";
	public final static String SQL_INSERT_GENRE_TABLE = "INSERT INTO GENRE (GENRENAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_LANGUAGE_TABLE = "INSERT INTO LANGUAGE (LANGANAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_TYPE_TABLE = "INSERT INTO TYPE (TYPENAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_SCREENSHOT_TABLE = "INSERT INTO SCREENSHOT (SCREENSHOTNAME,INVENTORYTYPE,INVENTORYID,IMAGE,ADDEDBY) VALUES(?,?,?,?,?)";
	public final static String SQL_INSERT_QUALITY_TABLE = "INSERT INTO QUALITY (QUALITYNAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_LOCATION_TABLE = "INSERT INTO LOCATION (LOCATIONNAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	public final static String SQL_INSERT_PUBLICATION_TABLE = "INSERT INTO PUBLICATION (PUBLICATIONNAME,INVENTORYTYPE,ADDEDBY) VALUES(?,?,?)";
	
	public final static String SQL_INSERT_BOOK_TABLE = "INSERT INTO BOOK (BOOKNAME,AUTHORNAME,PRICE,COMMENTS,PUBLICATIONID,TYPEID,GENREID,LANGID,QUALITYID,LOCATIONID,SUBCATEGORYID,CATEGORYID,INVENTORYTYPE,ISACTIVE,ADDEDBY) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	public final static String SQL_SELECT_ALL_CATEGORY = "SELECT * FROM CATEGORY WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_SUBCATEGORY = "SELECT * FROM SUBCATEGORY WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_GENRE = "SELECT * FROM GENRE WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_LANGUAGE = "SELECT * FROM LANGUAGE WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_TYPE = "SELECT * FROM TYPE WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_SCREENSHOT_BY_INVENTORY = "SELECT * FROM SCREENSHOT WHERE INVENTORYTYPE=? AND INVENTORYID=?";
	public final static String SQL_SELECT_ALL_QUALITY = "SELECT * FROM QUALITY WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_LOCATION = "SELECT * FROM LOCATION WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_PUBLICATION = "SELECT * FROM PUBLICATION WHERE INVENTORYTYPE=?";
	public final static String SQL_SELECT_ALL_BOOK = "SELECT * FROM BOOK WHERE INVENTORYTYPE=?";

	public final static String SQL_DELETE_ALL_CATEGORY = "DELETE  FROM CATEGORY WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_SUBCATEGORY = "DELETE  FROM SUBCATEGORY WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_GENRE = "DELETE  FROM GENRE WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_LANGUAGE = "DELETE  FROM LANGUAGE WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_TYPE = "DELETE  FROM TYPE WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_SCREENSHOT_BY_INVENTORY = "DELETE  FROM SCREENSHOT WHERE INVENTORYTYPE=? AND INVENTORYID=?";
	public final static String SQL_DELETE_ALL_QUALITY = "DELETE  FROM QUALITY WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_LOCATION = "DELETE  FROM LOCATION WHERE INVENTORYTYPE=?";
	public final static String SQL_DELETE_ALL_PUBLICATION = "DELETE  FROM PUBLICATION WHERE INVENTORYTYPE=?";

	public final static String SQL_VALIDATE_USER = "SELECT password FROM user WHERE USER_ID=?";
	public final static String SQL_CHECK_USER_NAME = "SELECT user_id FROM user WHERE USER_ID=?";
	public final static String SQL_INSERT_USER = "INSERT INTO USER (user_id, password,first_name,last_name,address,city,state,zip,phone,email,secques,secans) "
			+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
	public final static String SQL_GET_EMAIL_ID = "SELECT email FROM user WHERE USER_ID=?";
	public final static String SQL_GET_PASSWORD = "SELECT password FROM user WHERE USER_ID=?";
	public final static String SQL_GET_USER_DETAIL = "SELECT * FROM user WHERE ID=?";
	public final static String SQL_UPDATE_USER_DETAIL = "UPDATE user SET user_id=?, password=?, phone=?,email=? WHERE id=?";

	public final static String SQL_GET_USERS = "SELECT * FROM user";
	public final static String SQL_GET_ALL_USERS = "SELECT * FROM user";

	public final static String MESSAGE_INVALID_USERID = "User Id does not exists";
	public final static String MESSAGE_INVALID_LOGIN = "UserID/Passsword  does not match";
	public final static String MESSAGE_VALID_LOGIN = "Login Sucess";
}
